﻿
using famails.common;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace famails.dao
{
    /// <summary>
    /// Summary description for ClientRegisterDAO
    /// </summary>
    public class ClientRegisterDAO
    {
        public ClientRegisterDAO()
        {

        }
        public DataTable GetByID(int Id)
        {
            SqlCommand cmd = new SqlCommand("Select * from tblClientRegister where registerId=@registerId", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@registerId", SqlDbType.NVarChar).Value = Id;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            if (ConnectionData._MyConnection.State == ConnectionState.Closed)
            {
                ConnectionData._MyConnection.Open();
            }
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }
        public DataTable Search_client_register(string clientName, string namepackagelimit, string registerTime_from, string registerTime_to, string expireDate_from, string expireDate_to)
        {
            SqlCommand cmd = new SqlCommand("Client_Search_register", ConnectionData._MyConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@clientName", SqlDbType.NVarChar).Value = clientName;
            cmd.Parameters.Add("@namepackagelimit", SqlDbType.Int).Value = namepackagelimit;
            cmd.Parameters.Add("@registerTime_from", SqlDbType.VarChar, 12).Value = registerTime_from;
            cmd.Parameters.Add("@registerTime_to", SqlDbType.VarChar, 12).Value = registerTime_to;
            cmd.Parameters.Add("@expireDate_from", SqlDbType.VarChar, 12).Value = expireDate_from;
            cmd.Parameters.Add("@expireDate_to", SqlDbType.VarChar, 12).Value = expireDate_to;
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            if (ConnectionData._MyConnection.State == ConnectionState.Closed)
            {
                ConnectionData._MyConnection.Open();
            }
            adapter.Fill(table);
            cmd.Dispose();
            adapter.Dispose();
            return table;
        }
        public DataTable GetAllPackage()
        {
            SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM tblPackageLimit",
                ConnectionData._MyConnection);
            DataTable table = new DataTable();
            adapter.Fill(table);
            adapter.Dispose();
            return table;
        }
        public int UpdateUpgrade(int clientId, int packageId, int limitId, int SubAccount, float totalFee, int registerType, int packagetimeid, string From, string To, string LastRegisterFrom, string LastRegisterTo, int LastRegisterFee, int LastRegisterFeeRemain)
        {
            string sql = "set dateformat dmy Insert Into tblClientRegister (clientId,packageId,limitId,subAccontCount,totalFee,registerType,packageTimeId,[from],[to],lastRegisterFrom,lastRegisterTo,lastRegisterFee,lastRegisterFeeRemain,registerTime,registerDate) values (@clientId,@packageId,@limitId,@subAccontCount,@totalFee,@registerType,@packageTimeId,@from,@to,@lastRegisterFrom,@lastRegisterTo,@lastRegisterFee,@lastRegisterFeeRemain,getdate(),'" + DateTime.Now.ToString("dd/MM/yyyy") + "') Select @@identity";
            SqlCommand cmd = new SqlCommand(sql, ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@clientId", SqlDbType.Int).Value = clientId;
            cmd.Parameters.Add("@packageId", SqlDbType.Int).Value = packageId;
            cmd.Parameters.Add("@limitId", SqlDbType.Int).Value = limitId;
            cmd.Parameters.Add("@subAccontCount", SqlDbType.Int).Value = SubAccount;
            cmd.Parameters.Add("@totalFee", SqlDbType.Float).Value = totalFee;
            cmd.Parameters.Add("@registerType", SqlDbType.Int).Value = registerType;
            cmd.Parameters.Add("@packageTimeId", SqlDbType.Int).Value = packagetimeid;
            cmd.Parameters.Add("@from", SqlDbType.VarChar).Value = From;
            cmd.Parameters.Add("@to", SqlDbType.VarChar).Value = To;
            cmd.Parameters.Add("@lastRegisterFrom", SqlDbType.VarChar).Value = LastRegisterFrom;
            cmd.Parameters.Add("@lastRegisterTo", SqlDbType.VarChar).Value = LastRegisterTo;
            cmd.Parameters.Add("@lastRegisterFee", SqlDbType.Float).Value = LastRegisterFee;
            cmd.Parameters.Add("@lastRegisterFeeRemain", SqlDbType.Float).Value = LastRegisterFeeRemain;
            //cmd.Parameters.Add("@registerTime", SqlDbType.VarChar).Value = registerTime;
            //cmd.Parameters.Add("@registerDate", SqlDbType.VarChar).Value = registerDate;
            object id = cmd.ExecuteScalar();
            cmd.Dispose();
            return Int32.Parse(id + "");
        }

        internal DataTable GetByUserId(int UserID)
        {
            SqlDataAdapter adapter = new SqlDataAdapter("SELECT emailCount, [to] FROM tblClientRegister where registerid = (select registerid from tblClient where userid=" + UserID + ")",
                ConnectionData._MyConnection);
            DataTable table = new DataTable();
            adapter.Fill(table);
            adapter.Dispose();
            return table;
        }
        internal DataTable GetBySubUserId(int UserID)
        {
            SqlDataAdapter adapter = new SqlDataAdapter("SELECT emailCount,[to] FROM tblClientRegister where registerid = (select registerid from tblClient where clientid =(select clientid from tblSubClient where userid=" + UserID + "))",
                ConnectionData._MyConnection);
            DataTable table = new DataTable();
            adapter.Fill(table);
            adapter.Dispose();
            return table;
        }

        internal bool UpdateUpgrade(dto.clientRegisterdto dto, List<object> functions)
        {
            SqlTransaction tran=null;
            try
            {
                string sql = "set dateformat dmy Insert Into tblClientRegister (clientId,packageId,limitId,subAccontCount,totalFee, feeperday,registerType,packageTimeId,[from],[to],emailcount, registerTime,registerDate,TransactionCode) values (@clientId,@packageId,@limitId,@subAccontCount,@totalFee, @feeperday,@registerType,@packageTimeId,@from,@to,@emailCount,getdate(),'" + DateTime.Now.ToString("dd/MM/yyyy") + "',@TransactionCode) Select @@identity";
                SqlCommand cmd = new SqlCommand(sql, ConnectionData._MyConnection);
                tran = ConnectionData._MyConnection.BeginTransaction();
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@clientId", SqlDbType.Int).Value = dto.clientId;
                cmd.Parameters.Add("@packageId", SqlDbType.Int).Value = dto.packageId;
                cmd.Parameters.Add("@limitId", SqlDbType.Int).Value = dto.limitId;
                cmd.Parameters.Add("@subAccontCount", SqlDbType.Int).Value = dto.subAccontCount;
                cmd.Parameters.Add("@emailCount", SqlDbType.Int).Value = dto.emailCount;
                cmd.Parameters.Add("@totalFee", SqlDbType.Float).Value = dto.totalFee;
                cmd.Parameters.Add("@FeePerDay", SqlDbType.Float).Value = dto.FeePerDay;
                cmd.Parameters.Add("@FeePerMonth", SqlDbType.Float).Value = dto.FeePerMonth;
                cmd.Parameters.Add("@registerType", SqlDbType.Int).Value = dto.registerType;
                cmd.Parameters.Add("@packageTimeId", SqlDbType.Int).Value = dto.packageTimeId;
                cmd.Parameters.Add("@from", SqlDbType.VarChar).Value = dto.from.ToString("dd/MM/yyyy");
                cmd.Parameters.Add("@to", SqlDbType.VarChar).Value = dto.to.ToString("dd/MM/yyyy");
                cmd.Parameters.Add("@TransactionCode", SqlDbType.NVarChar).Value = dto.TransactionCode;
                object id = cmd.ExecuteScalar();

                //sql = "set dateformat dmy update tblclient set lastRegisterId=registerid, registerid = @registerid, expireDate=@expireDate where clientid = @clientid";
                //cmd = new SqlCommand(sql, ConnectionData._MyConnection);
                //cmd.Transaction = tran;
                //cmd.CommandType = CommandType.Text;
                //cmd.Parameters.Add("@registerid", SqlDbType.Int).Value = id;
                //cmd.Parameters.Add("@clientid", SqlDbType.Int).Value = dto.clientId;
                //cmd.Parameters.Add("@expireDate", SqlDbType.VarChar).Value = dto.to.ToString("dd/MM/yyyy");
                //cmd.ExecuteNonQuery();

                foreach (var obj in functions)
                {
                    sql = "insert into tblClientFunction(clientid, functionid, registerid) values(@clientid, @functionid, @registerid)";
                    cmd = new SqlCommand(sql, ConnectionData._MyConnection);
                    cmd.Transaction = tran;
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@registerid", SqlDbType.Int).Value = id;
                    cmd.Parameters.Add("@clientid", SqlDbType.Int).Value = dto.clientId;
                    cmd.Parameters.Add("@functionid", SqlDbType.Int ).Value = obj;
                    cmd.ExecuteNonQuery();
                }
                tran.Commit();
                return true;
            }
            catch (Exception ex) {
                if (tran != null) tran.Rollback();
                throw ex;
            }
        }

        internal bool Extend(int ClientID, double cost, double costperday, double costpermonth, DateTime from, DateTime to, int PackageTimeID)
        {
            SqlTransaction tran = null;
            try
            {
                string sql = "select * From tblClientRegister where registerid = (select registerid from tblClient where clientid="+ClientID+")";
                SqlCommand cmd = new SqlCommand(sql, ConnectionData._MyConnection);
                tran = ConnectionData._MyConnection.BeginTransaction();
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.Text;
                SqlDataAdapter data = new SqlDataAdapter(cmd);
                DataTable T = new DataTable();
                data.Fill(T);

                sql = "select * From tblClientFunction where registerid = (select registerid from tblClient where clientid=" + ClientID + ")";
                cmd = new SqlCommand(sql, ConnectionData._MyConnection);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.Text;
                data = new SqlDataAdapter(cmd);
                DataTable functions = new DataTable();
                data.Fill(T);

                sql = "set dateformat dmy Insert Into tblClientRegister (clientId,packageId,limitId,subAccontCount,totalFee, feeperday, feepermonth,registerType,packageTimeId,[from],[to],emailcount, registerTime,registerDate) values (@clientId,@packageId,@limitId,@subAccontCount,@totalFee, @feeperday, @feepermonth,@registerType,@packageTimeId,@from,@to,@emailCount,getdate(),'" + DateTime.Now.ToString("dd/MM/yyyy") + "') Select @@identity";
                cmd = new SqlCommand(sql, ConnectionData._MyConnection);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@clientId", SqlDbType.Int).Value = T.Rows[0]["clientId"];
                cmd.Parameters.Add("@packageId", SqlDbType.Int).Value = T.Rows[0]["packageId"];
                cmd.Parameters.Add("@limitId", SqlDbType.Int).Value = T.Rows[0]["limitId"];
                cmd.Parameters.Add("@subAccontCount", SqlDbType.Int).Value = T.Rows[0]["subAccontCount"];
                cmd.Parameters.Add("@emailCount", SqlDbType.Int).Value = T.Rows[0]["emailCount"];
                cmd.Parameters.Add("@totalFee", SqlDbType.Float).Value = cost;
                cmd.Parameters.Add("@FeePerDay", SqlDbType.Float).Value = costperday;
                cmd.Parameters.Add("@FeePerMonth", SqlDbType.Float).Value = costpermonth;
                cmd.Parameters.Add("@registerType", SqlDbType.Int).Value = 1;
                cmd.Parameters.Add("@packageTimeId", SqlDbType.Int).Value = PackageTimeID;
                cmd.Parameters.Add("@from", SqlDbType.VarChar).Value = from.ToString("dd/MM/yyyy");
                cmd.Parameters.Add("@to", SqlDbType.VarChar).Value = to.ToString("dd/MM/yyyy");
                object id = cmd.ExecuteScalar();

                sql = "set dateformat dmy update tblclient set lastRegisterId=registerid, registerid = @registerid, expireDate=@expireDate where clientid = @clientid";
                cmd = new SqlCommand(sql, ConnectionData._MyConnection);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@registerid", SqlDbType.Int).Value = id;
                cmd.Parameters.Add("@clientid", SqlDbType.Int).Value = ClientID;
                cmd.Parameters.Add("@expireDate", SqlDbType.VarChar).Value = to.ToString("dd/MM/yyyy");
                cmd.ExecuteNonQuery();

                foreach (DataRow obj in functions.Rows)
                {
                    sql = "insert into tblClientFunction(clientid, functionid, registerid) values(@clientid, @functionid, @registerid)";
                    cmd = new SqlCommand(sql, ConnectionData._MyConnection);
                    cmd.Transaction = tran;
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@registerid", SqlDbType.Int).Value = id;
                    cmd.Parameters.Add("@clientid", SqlDbType.Int).Value = ClientID;
                    cmd.Parameters.Add("@functionid", SqlDbType.Int).Value = obj["functionid"];
                    cmd.ExecuteNonQuery();
                }
                tran.Commit();
                return true;
            }
            catch (Exception ex)
            {
                if (tran != null) tran.Rollback();
                throw ex;
            }
        }

        internal DataTable GetUnPaymentByClientID(int clientId)
        {
            string sql = "Select * From tblClientRegister where clientid=@clientid and ispaid=0";
            SqlCommand cmd = new SqlCommand(sql, ConnectionData._MyConnection);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@clientid", SqlDbType.Int).Value = clientId;
            SqlDataAdapter data = new SqlDataAdapter(cmd);
            DataTable result = new DataTable();
            data.Fill(result);
            return result;
        }
    }
}